﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Employee_Deduction_MyList]
	(
	@startIndex int,
	@endindex int,
	@Month nvarchar(50),
	@userid varchar(50)	
	)
AS
BEGIN
		
	--ROW_NUMBER() OVER (Order By ApplionTime DESC)AS Row,
;WITH list as (
select ROW_NUMBER() OVER (Order By FirstAuditDate DESC)AS Row,* from (
select [ApplionMoney],Reason,[Companyid],departmentid,[EName],
	Convert(varchar(10),FirstAuditDate,120) as FirstAuditDate,[ApplionMonth]
	
from Employee_Deduction where username=@userid and flag=1 and (ApplionMonth =@Month or @Month='' )

union
	select koukuanamount,isnull((select title from Employee_Attendance_Type a where a.id=b.typeid),'')+'('+[description]+')' as Reason,
	companyid,departmentid,ename,createdate,Convert(varchar(7),KouKuanFromDate,120) as [ApplionMonth] 
 from [Employee_Attendance] as b
 where username=@userid and Convert(varchar(7),KouKuanFromDate,120)=@Month
) as nb
)

 select *,(select companyname from company where company.id=companyid) as companyname
			  ,(select DName from Department where id=departmentid) as DepartmentName
			  ,(select count(1) from list ) as coun
			  ,(select sum([ApplionMoney]) from list) as SumMoney
			  
 from list where row >=@startIndex and row <= @endindex


END
